#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2024/1/30 15:10
# @Author  : 王凯
# @File    : gupiao_ths.py
# @Project : scrapy_spider
import datetime
import decimal
import json
import re

import scrapy

from apps.tonghuashun.tonghuashun.items import NetThsListedCompanyCashflowItem, NetThsListedCompanyBalanceItem, NetThsListedCompanyProfitItem
from utils.tonghuashun_tools import PostgresqlDB, TONGHUASHUN_CONFIG


def get_previous_quarters(n):
    today = datetime.date.today()
    quarter = (today.month - 1) // 3 + 1
    year = today.year
    quarters = []
    for i in range(n):
        quarter -= 1
        if quarter == 0:
            quarter = 4
            year -= 1
        last_month_of_quarter = quarter * 3
        last_day_of_month = 31 if last_month_of_quarter in [1, 3, 5, 7, 8, 10, 12] else 30
        last_date_of_quarter = datetime.date(year, last_month_of_quarter, last_day_of_month)
        first_date_of_quarter = datetime.date(last_date_of_quarter.year, last_date_of_quarter.month - 2, 1)
        quarters.append(str(last_date_of_quarter))
    return quarters


class GupiaoThsSpiderSpider(scrapy.Spider):
    name = "gupiao_ths"
    Request = scrapy.Request
    _pg_db = None

    @property
    def pg_db(self):
        if self._pg_db is None:
            self._pg_db = PostgresqlDB(
                ip=TONGHUASHUN_CONFIG["ip"],
                port=TONGHUASHUN_CONFIG["port"],
                db=TONGHUASHUN_CONFIG["db"],
                user_name=TONGHUASHUN_CONFIG["user_name"],
                user_pass=TONGHUASHUN_CONFIG["user_pass"],
            )
        return self._pg_db

    def start_requests(self):
        yield self.Request("http://data.10jqka.com.cn/financial/yjgg/", method="GET", callback=self.parse)

    def parse(self, response, **kwargs):
        previous_quarters = get_previous_quarters(2)[0]
        total_page = response.xpath('//*[@class="page_info"]').re_first(r"/(\d+)")
        if total_page:
            total_page = int(total_page)
        for page in range(1, total_page + 1):
            url = f"http://data.10jqka.com.cn/ajax/yjgg/date/{previous_quarters}/board/ALL/field/DECLAREDATE/order/desc/page/{page}/ajax/1/free/1/"
            yield self.Request(url, method="GET", callback=self.parse_list)

    def parse_list(self, response, **kwargs):
        url_list = response.xpath("//tbody//tr//a[@code]")
        for url in url_list:
            name = url.xpath("./text()").get()
            code = url.xpath("./@code").get()
            src_url = url.xpath("./@href").get()
            company_url = src_url.replace("/finance/", "/company.html").replace(
                "http://stockpage.10jqka.com.cn/", "https://basic.10jqka.com.cn/"
            )
            yield self.Request(
                company_url,
                method="GET",
                callback=self.parse_company,
                cb_kwargs=dict(
                    name=name,
                    code=code,
                    src_url=src_url,
                )
            )

    def parse_company(self, response, **kwargs):
        src_url = kwargs.get('src_url')
        name = kwargs.get('name')
        code = kwargs.get('code')
        taxpayer_id = None
        fullname = response.xpath("//td[contains(string(), '公司名称')]/span/text()").get()
        if fullname:
            sql = """ SELECT corp_name AS company_name, unified_social_credit_code AS taxpayer_id FROM enterprise_basic_info  WHERE corp_name IN ({com_name_list_str}) AND isvalid = 1  UNION ALL  SELECT corp_name AS company_name, unified_social_credit_code AS taxpayer_id FROM individual_business_info  WHERE corp_name IN ({com_name_list_str}) AND isvalid = 1""".format(
                com_name_list_str=f"'{fullname}'"
            )
            taxpayer_id_list = self.pg_db.find(sql, to_json=True)
            if taxpayer_id_list:
                taxpayer_id = taxpayer_id_list[0]["taxpayer_id"]
        yield self.Request(
            src_url.replace("/finance/", "_cash.json").replace(
                "http://stockpage.10jqka.com.cn/", "https://basic.10jqka.com.cn/api/stock/finance/"
            ),
            method="GET",
            callback=self.parse_detail,
            cb_kwargs=dict(
                type_name="cash",  # 现金流量表
                name=name,
                code=code,
                fullname=fullname,
                taxpayer_id=taxpayer_id,
            )
        )
        yield self.Request(
            src_url.replace("/finance/", "_debt.json").replace(
                "http://stockpage.10jqka.com.cn/", "https://basic.10jqka.com.cn/api/stock/finance/"
            ),
            method="GET",
            callback=self.parse_detail,
            cb_kwargs=dict(
                type_name="debt",  # 知产负债表
                name=name,
                code=code,
                fullname=fullname,
                taxpayer_id=taxpayer_id,
            )
        )
        yield self.Request(
            src_url.replace("/finance/", "_benefit.json").replace(
                "http://stockpage.10jqka.com.cn/", "https://basic.10jqka.com.cn/api/stock/finance/"
            ),
            method="GET",
            callback=self.parse_detail,
            cb_kwargs=dict(
                type_name="benefit",  # 利润表
                name=name,
                code=code,
                fullname=fullname,
                taxpayer_id=taxpayer_id,
            )
        )

    @staticmethod
    def deal_money(money_str):
        amount = None
        if money_str:
            money = re.findall(r"([-.\d+]+)", money_str)
            if "万" in money_str:
                base = 10000
            elif "亿" in money_str:
                base = 100000000
            else:
                base = 1
            if money:
                find_money = money[0]
                amount = decimal.Decimal(find_money) * base
        return amount

    def parse_detail(self, response, **kwargs):
        name = kwargs.get("name")
        code = kwargs.get("code")
        if code:
            code = code.split("_")[-1]
        fullname = kwargs.get("fullname")
        taxpayer_id = kwargs.get("taxpayer_id")
        flash_data = json.loads(response.json()["flashData"])
        type_name = kwargs.get("type_name")
        title = flash_data.get("title")
        index_title = [i[0] + "_" + i[1] for i in title[1:]]
        import pandas as pd
        title_df = pd.DataFrame(title[1:])
        title_df.columns = ['project_name', 'currency_unit', 'col', 'blank', 'no_blank']
        title_df = title_df[['project_name', 'currency_unit']]
        title_df.index = range(1, len(title_df) + 1)

        year_df = pd.DataFrame(flash_data.get("year"))
        columns = year_df.iloc[0]
        year_df = year_df.iloc[1:]
        year_df.columns = columns
        year_total_df = pd.concat([title_df, year_df], axis=1)

        report_df = pd.DataFrame(flash_data.get("report"))
        columns = report_df.iloc[0]
        report_df = report_df.iloc[1:]
        report_df.columns = columns
        report_total_df = pd.concat([title_df, report_df], axis=1)

        simple_df = pd.DataFrame(flash_data.get("simple"))
        columns = simple_df.iloc[0]
        simple_df = simple_df.iloc[1:]
        simple_df.columns = columns
        simple_total_df = pd.concat([title_df, simple_df], axis=1)

        for report_type, value in year_total_df.set_index(['project_name', 'currency_unit']).to_dict().items():
            for idx, (k, v) in enumerate(value.items(), start=1):
                item = {
                    'project_name': k[0],
                    'currency_unit': k[1],
                    'report_type': report_type,
                    "years": str(report_type)[:4],
                    'amount': self.deal_money(v),
                    "index_col": idx,
                    "company_name": name,
                    "securities_code": code,
                    "fullname": fullname,
                    "taxpayer_id": taxpayer_id,
                    "period": '准年报',
                }
                if type_name == "cash":  # 现金流量表
                    yield NetThsListedCompanyCashflowItem(**item)
                    # net_ths_listed_company_cashflow
                elif type_name == "debt":  # 知产负债表
                    yield NetThsListedCompanyBalanceItem(**item)
                    # net_ths_listed_company_balance
                elif type_name == "benefit":  # 利润表
                    yield NetThsListedCompanyProfitItem(**item)
                    # net_ths_listed_company_profit

        for report_type, value in report_total_df.set_index(['project_name', 'currency_unit']).to_dict().items():
            for idx, (k, v) in enumerate(value.items(), start=1):
                if '03-31' in str(report_type):
                    period = '一季报'
                elif '06-30' in str(report_type):
                    period = '中报'
                elif '09-30' in str(report_type):
                    period = '三季报'
                else:
                    period = '年报'
                item = {
                    'project_name': k[0],
                    'currency_unit': k[1],
                    'report_type': report_type,
                    "years": str(report_type)[:4],
                    'amount': self.deal_money(v),
                    "index_col": idx,
                    "company_name": name,
                    "securities_code": code,
                    "fullname": fullname,
                    "taxpayer_id": taxpayer_id,
                    "period": period,
                }
                if type_name == "cash":  # 现金流量表
                    yield NetThsListedCompanyCashflowItem(**item)
                    # net_ths_listed_company_cashflow
                elif type_name == "debt":  # 知产负债表
                    yield NetThsListedCompanyBalanceItem(**item)
                    # net_ths_listed_company_balance
                elif type_name == "benefit":  # 利润表
                    yield NetThsListedCompanyProfitItem(**item)
                    # net_ths_listed_company_profit

        for report_type, value in simple_total_df.set_index(['project_name', 'currency_unit']).to_dict().items():
            for idx, (k, v) in enumerate(value.items(), start=1):
                if '03-31' in str(report_type):
                    period = 'Q1'
                elif '06-30' in str(report_type):
                    period = 'Q2'
                elif '09-30' in str(report_type):
                    period = 'Q3'
                else:
                    period = 'Q4'
                item = {
                    'project_name': k[0],
                    'currency_unit': k[1],
                    'report_type': report_type,
                    "years": str(report_type)[:4],
                    'amount': self.deal_money(v),
                    "index_col": idx,
                    "company_name": name,
                    "securities_code": code,
                    "fullname": fullname,
                    "taxpayer_id": taxpayer_id,
                    "period": period,
                }
                if type_name == "cash":  # 现金流量表
                    yield NetThsListedCompanyCashflowItem(**item)
                    # net_ths_listed_company_cashflow
                elif type_name == "debt":  # 知产负债表
                    yield NetThsListedCompanyBalanceItem(**item)
                    # net_ths_listed_company_balance
                elif type_name == "benefit":  # 利润表
                    yield NetThsListedCompanyProfitItem(**item)
                    # net_ths_listed_company_profit


if __name__ == "__main__":
    from scrapy import cmdline

    cmdline.execute("scrapy crawl gupiao_ths".split())
